package uk.ac.cam.juliet.zedcat.db;

/* An interface to the MySQL databae for the image analysis module */
/* Written by Thomas Alexander */
/* Started on 31st January, 2010 */

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;

public class ImageAnalysisDatabase extends JulietDbInterface{
	public ImageAnalysisDatabase(String address) throws SQLException, ClassNotFoundException
	{
		super(address, "image.analysis", "ghlenlivid");
	}
	
	public ImageAnalysisDatabase(String address, String username, String password) throws SQLException, ClassNotFoundException
	{
		super(address, username, password);
	}
	
	public List<ImageHotspot> getHotspots() throws SQLException
	{
		//Will return a list of all of the hotspots in tblHotspot
		//See ImageHotspot.java
		
		String stmt = "SELECT HotspotID, x1, y1, x2, y2 FROM tblHotspot";
		PreparedStatement hotspotQuery = databaseConnection.prepareStatement(stmt);
		List<ImageHotspot> hotspotList = new LinkedList<ImageHotspot>();
		
		ResultSet hotspotRs = null;
		try{
			hotspotRs = hotspotQuery.executeQuery();
			ImageHotspot currentHotspot;
			while(hotspotRs.next())
			{
				currentHotspot = new ImageHotspot(hotspotRs.getInt(1), hotspotRs.getInt(2), hotspotRs.getInt(3), hotspotRs.getInt(4), hotspotRs.getInt(5));
				hotspotList.add(currentHotspot);
			}
		}finally{
			hotspotRs.close();
		}
		return hotspotList;
	}
	
	public String generateSightingPk(Timestamp date) throws SQLException
	{
		//Will return a sighting ID (primary key) of the form DDMMYYhhmmXXXXXX
		//Where XXXXXX is a number from 0 - 999999, allocated in ascending order
		
		//A variable for our new primary key
		String sightingPk;
		
		//Calendar to work out the dates to include in our search
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		
		//Zero the second and millisecond fields
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		
		//Store the result
		Timestamp dateLow = new Timestamp(cal.getTimeInMillis());
		
		//Add one to the minutes field and store the result
		cal.add(Calendar.MINUTE, 1);
		Timestamp dateHigh = new Timestamp(cal.getTimeInMillis());
		
		//We're going to want to format the date later on.
		//Makes sense to do it now, before we branch!
		SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyykkss");
		
		//Now we want all of the records between dateLow and dateHigh
		String searchStmt = "SELECT SightingId, Time from tblSighting WHERE (TIMEDIFF(Time, ?) >= 0) AND (TIMEDIFF(?, Time) > 0) ORDER BY Time";
		PreparedStatement searchStatement = databaseConnection.prepareStatement(searchStmt);
		searchStatement.setTimestamp(1, dateLow);
		searchStatement.setTimestamp(2, dateHigh);
		ResultSet results = searchStatement.executeQuery();
		
		if(!results.last())
		{
			//Then the query returned no rows!
			sightingPk = dateFormat.format(date) + "000000";
			results.close();
			searchStatement.close();
			return sightingPk;
		}
		
		//If we get here, then we have to append a 6-digit number in ascending order
		String highestNumStr = results.getString(1).substring(10);
		results.close();
		searchStatement.close();
		int highestNum = Integer.parseInt(highestNumStr);
		highestNum ++;
		String newSuffix = Integer.toString(highestNum);
		//Pad out the beginning of the suffix with '0's
		while(newSuffix.length() < 6)
		{
			newSuffix = "0" + newSuffix;
		}
		
		//Now compose the new pk and return!
		sightingPk = dateFormat.format(date) + newSuffix;
		//System.out.println(sightingPk);
		return sightingPk;
	}
	
	public void addSighting(Timestamp date, String eventId, long hotspotId, String snapshotFilepath) throws SQLException
	{
		//Will insert a new sighting to tblSighting with an automatically generated PK (see generateSightingPk())
		//and the specified arguments
		//If you don't want to add a snapshot filepath, just pass a null
		String stmt = "INSERT INTO tblSighting (SightingID, HotspotID, Time, EventID, SnapshotFilepath) VALUES (?, ?, ?, ?, ?)";
		PreparedStatement insertStatement = databaseConnection.prepareStatement(stmt);
		
		//Prepare the arguments
		insertStatement.setString(1, generateSightingPk(date));
		insertStatement.setLong(2, hotspotId);
		insertStatement.setTimestamp(3, date);
		insertStatement.setString(4, eventId);
		if(snapshotFilepath != null)
			insertStatement.setString(5, snapshotFilepath);
		else
			insertStatement.setString(5, "");
		
		insertStatement.executeUpdate();
		insertStatement.close();
		databaseConnection.commit();
	}

        public String exitID() throws SQLException {
            //Get the ID of the 'Exit' event
            String exitIDStmt = "SELECT EventID from tblEvent WHERE STRCMP(tblEvent.Desc, \"Exit\") = 0";
            PreparedStatement exitIDQuery = databaseConnection.prepareStatement(exitIDStmt);
            ResultSet exitIDResultSet = exitIDQuery.executeQuery();
            if(!exitIDResultSet.first())
            {
                    System.err.println("Could not find an 'Exit' event... You're screwed");
            }
            String exitID = exitIDResultSet.getString(1);
            return exitID;
        }

        public String entryID() throws SQLException {
            //Get the ID of the 'Exit' event
            String exitIDStmt = "SELECT EventID from tblEvent WHERE STRCMP(tblEvent.Desc, \"Enter\") = 0";
            PreparedStatement exitIDQuery = databaseConnection.prepareStatement(exitIDStmt);
            ResultSet exitIDResultSet = exitIDQuery.executeQuery();
            if(!exitIDResultSet.first())
            {
                    System.err.println("Could not find an 'Exit' event... You're screwed");
            }
            String exitID = exitIDResultSet.getString(1);
            return exitID;
        }
	
	public void addSighting(String eventId, long hotspotId, String snapshotFilepath) throws SQLException
	{
		//Add a sighting with the current date/time
		java.util.Date utilDate = new java.util.Date();
		Timestamp sqlDate = new Timestamp(utilDate.getTime());
		addSighting(sqlDate, eventId, hotspotId, snapshotFilepath);
	}
}
